from flask import Blueprint, render_template, jsonify, request
from python.extensions import db
from python.model.weather import WeatherData
from sqlalchemy import func, extract
import datetime
from sqlalchemy.sql import label

# 创建天气统计分析蓝图，URL前缀为/menu
weather_stats_bp = Blueprint('weather_stats', __name__, url_prefix='/menu')


@weather_stats_bp.route('/city_weather_stats', methods=['GET'])
def city_weather_stats():
    """提供城市天气类型统计数据"""
    urban = request.args.get('urban', '')

    if not urban:
        return jsonify({"error": "城市参数不能为空"}), 400

    try:
        # 查询指定城市的所有天气类型及其出现次数
        query = db.session.query(
            WeatherData.weather.label('weather_type'),
            func.count(WeatherData.weather).label('count')
        ).filter(WeatherData.city == urban)

        # 按天气类型分组并排序（按出现次数降序）
        query = query.group_by(WeatherData.weather).order_by(func.count(WeatherData.weather).desc())

        results = query.all()

        if not results:
            return jsonify({
                "weather_types": [],
                "counts": [],
                "total_days": 0,
                "start_date": "",
                "end_date": "",
                "update_time": datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                "error": f"未找到 {urban} 的天气数据"
            })

        # 提取天气类型和对应的计数
        weather_types = [result.weather_type for result in results]
        counts = [result.count for result in results]

        # 获取数据时间范围
        date_query = db.session.query(
            func.min(WeatherData.date).label('min_date'),
            func.max(WeatherData.date).label('max_date')
        ).filter(WeatherData.city == urban)

        date_results = date_query.first()
        start_date = date_results.min_date.strftime("%Y-%m-%d") if date_results.min_date else ""
        end_date = date_results.max_date.strftime("%Y-%m-%d") if date_results.max_date else ""

        # 计算总天数
        total_days = (
                                 date_results.max_date - date_results.min_date).days + 1 if date_results.min_date and date_results.max_date else 0

        return jsonify({
            "weather_types": weather_types,
            "counts": counts,
            "total_days": total_days,
            "start_date": start_date,
            "end_date": end_date,
            "update_time": datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        })

    except Exception as e:
        print(f"获取城市天气统计数据时出错: {e}")
        return jsonify({"error": "获取数据时出错，请稍后重试"}), 500


@weather_stats_bp.route('/weather_stats', methods=['GET'])
def weather_stats():
    """渲染城市天气类型统计页面"""
    # 获取所有城市
    cities = db.session.query(WeatherData.city).distinct().all()
    cities = [city[0] for city in cities]

    # 预计算每个城市的天气数据天数
    cities_weather_counts = {}
    for city in cities:
        count = db.session.query(func.count(WeatherData.id)).filter(WeatherData.city == city).scalar()
        cities_weather_counts[city] = count or 0

    # 默认选择第一个城市
    initial_urban = cities[0] if cities else ""

    return render_template(
        'menu/city_weather_stats.html',
        cities=cities,
        cities_weather_counts=cities_weather_counts,
        urban=initial_urban,
        weather_types=[],
        counts=[],
        total_days=0,
        start_date="",
        end_date="",
        update_time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    )